package com.example.mapper;

import com.example.pojo.News;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface NewsMapper {

    //查询所有新闻
    @Select("SELECT n.id, n.title, n.publish_time, n.source, n.url, n.content, n.category_id categoryId, c.category_name categoryName " +
            "FROM news n " +
            "LEFT JOIN news_category c ON n.category_id = c.id")
    List<News> findAll();
    //根据id查询新闻
    @Select("SELECT n.id, n.title, n.publish_time, n.source, n.url, n.content, n.category_id categoryId, c.category_name categoryName " +
            "FROM news n " +
            "LEFT JOIN news_category c ON n.category_id = c.id " +
            "WHERE n.id = #{id}")
    News findById(@Param("id") Integer id);


    //根据分类id查询新闻
    @Select("SELECT n.id, n.title, n.publish_time, n.source, n.url, n.content, n.category_id categoryId, c.category_name categoryName " +
            "FROM news n " +
            "LEFT JOIN news_category c ON n.category_id = c.id " +
            "WHERE c.id = #{categoryId}")
    List<News> findByCategoryId(Integer categoryId);


    //根据id删除新闻
    @Delete("delete from news where id = #{id}")
    void deleteById(Integer id);

    //插入新闻
    @Insert("insert into news(title, publish_time, source, url, content,category_id) values(#{title}, #{publishTime}, #{source}, #{url}, #{content},#{categoryId})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(News news);
    //根据分类名称查询分类id
    @Select("SELECT id FROM news_category WHERE category_name = #{categoryName}")
    Integer findCategoryIdByName(String categoryName);

    // 插入新闻和分类的关系
    @Insert("insert into news_category_relation(news_id, category_id) values(#{newsId}, #{categoryId})")
    void insertNewsCategoryRelation(@Param("newsId") Integer newsId, @Param("categoryId") Integer categoryId);

    @Update("UPDATE news SET title = #{title}, publish_time = #{publishTime}, source = #{source}, url = #{url}, content = #{content}, category_id = #{categoryId} WHERE id = #{id}")
    void updateById(News news);

}
